﻿Imports System.Data.SqlClient

Public Class frmRVueltas
    Dim Bitacora As New clsActividad
    Dim Usuario As New clsUsuarioPrincipal
    Private Sub frmRLiquidacion_FormClosed(ByVal sender As Object, ByVal e As System.Windows.Forms.FormClosedEventArgs) Handles Me.FormClosed
        Bitacora.RegistraActividad("Cerró al Reporte de Vueltas")
        Usuario.Ubicacion("Módulo de Nóminas")
    End Sub
    Private Sub frmRVueltas_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        ToolStrip1.Cursor = Cursors.Hand
        ToolStrip1.RenderMode = System.Windows.Forms.ToolStripRenderMode.System

        datFecha1.Value = Now()
        RadioButton1.Checked = True

        lstA.Items.Clear()
        lstR.Items.Clear()
        cargaAutobuses()
        CargaRutas()


        Bitacora.RegistraActividad("Ingresó al Reporte de Vueltas")
        Usuario.Ubicacion("Reporte de Vueltas")
    End Sub
    Sub CargaRutas()
        lstRuta.Items.Clear()

        Dim cnconn As New SqlConnection
        Dim strSql As String = ""
        Dim cmdBuscar As New SqlCommand
        Dim rdBuscar As SqlDataReader

        Try
            cnconn.ConnectionString = CitraConnection

            strSql = "SELECT Nombre FROM RUTAS"

            cmdBuscar.Connection = cnconn
            cmdBuscar.CommandText = strSql

            cnconn.Open()
            rdBuscar = cmdBuscar.ExecuteReader

            Do While rdBuscar.Read()
                Me.lstRuta.Items.Add(rdBuscar("Nombre").ToString.Trim)
            Loop
        Catch ex As SqlException
            MsgBox(ex.Message, MsgBoxStyle.Exclamation, "Aviso")
        Finally
            If cnconn.State = ConnectionState.Open Then
                cnconn.Close()
            End If
        End Try
    End Sub
    Sub cargaAutobuses()
        lstAutobus.Items.Clear()

        Dim cnconn As New SqlConnection
        Dim strSql As String = ""
        Dim cmdBuscar As New SqlCommand
        Dim rdBuscar As SqlDataReader

        Try
            cnconn.ConnectionString = CitraConnection

            strSql = "select NoEconomico from Autobuses where Estatus=1 ORDER BY NoEconomico"

            cmdBuscar.Connection = cnconn
            cmdBuscar.CommandText = strSql

            cnconn.Open()
            rdBuscar = cmdBuscar.ExecuteReader
            Dim primero As Boolean
            primero = True

            Do While rdBuscar.Read()
                Me.lstAutobus.Items.Add(rdBuscar("NoEconomico").ToString.Trim)
            Loop
        Catch ex As SqlException
            MsgBox(ex.Message, MsgBoxStyle.Exclamation, "Aviso")
        Finally
            If cnconn.State = ConnectionState.Open Then
                cnconn.Close()
            End If
        End Try

    End Sub
    Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click
        Dim aux As String = ""
        aux = lstRuta.Text.ToString.Trim
        If aux <> "" Then
            lstR.Items.Add(lstRuta.SelectedItem)
            lstRuta.Items.Remove(lstRuta.SelectedItem)
        End If
    End Sub

    Private Sub Button4_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button4.Click
        Dim aux As String = ""
        aux = lstR.Text.ToString.Trim
        If aux <> "" Then
            lstRuta.Items.Add(lstR.SelectedItem)
            lstR.Items.Remove(lstR.SelectedItem)
        End If
    End Sub

    Private Sub Button5_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button5.Click
        Dim aux As String = ""
        aux = lstAutobus.Text.ToString.Trim
        If aux <> "" Then
            lstA.Items.Add(lstAutobus.SelectedItem)
            lstAutobus.Items.Remove(lstAutobus.SelectedItem)
        End If
    End Sub

    Private Sub Button6_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button6.Click
        Dim aux As String = ""
        aux = lstA.Text.ToString.Trim
        If aux <> "" Then
            lstAutobus.Items.Add(lstA.SelectedItem)
            lstA.Items.Remove(lstA.SelectedItem)
        End If
    End Sub

    Private Sub RadioButton1_CheckedChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles RadioButton1.CheckedChanged
        lstA.Enabled = False
        lstAutobus.Enabled = False
        lstR.Enabled = False
        lstRuta.Enabled = False
    End Sub

    Private Sub RadioButton2_CheckedChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles RadioButton2.CheckedChanged
        lstA.Enabled = False
        lstAutobus.Enabled = False
        lstR.Enabled = True
        lstRuta.Enabled = True
    End Sub

    Private Sub RadioButton3_CheckedChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles RadioButton3.CheckedChanged
        lstA.Enabled = True
        lstAutobus.Enabled = True
        lstR.Enabled = False
        lstRuta.Enabled = False
    End Sub
    Sub Todos()
        Dim xl As Object
        Dim wb As Object
        Dim i As Integer
        xl = CreateObject("Excel.Application")
        xl.Visible = True
        xl.Workbooks.Add()
        wb = xl.activeworkbook
        Dim rng As Integer
        rng = 5

        wb.SHEETS(1).cells(1, 1).value = "Imprimiendo..."
        wb.SHEETS(1).columns(1).columnwidth = 15

        Dim strFecha1 As String = datFecha1.Value.ToString("yyyy-dd-MM")
        Dim strFecha2 As String = datFecha2.Value.AddDays(1).ToString("yyyy-dd-MM")

        Dim cnconn As New SqlConnection
        cnconn.ConnectionString = CitraConnection
        Dim strSql As String = ""
        cnconn.Open()
        Dim cmdBuscar As New SqlCommand
        cmdBuscar.Connection = cnconn

        strSql = " SELECT IdLiquidacion,NombreRuta,NoEconomico,CodigoOperador,NombreOperador,Vueltas,Fecha " & _
                 " FROM Liquidacion " & _
                 " WHERE Fecha>='" & strFecha1 & "' AND Fecha<='" & strFecha2 & "'" & _
                 " ORDER BY NombreOperador,NombreRuta"

        cmdBuscar.CommandText = strSql
        Dim rdBuscar As SqlDataReader
        rdBuscar = cmdBuscar.ExecuteReader
        Dim strCamion As String = "?"

        wb.SHEETS(1).columns(2).columnwidth = 40
        wb.SHEETS(1).columns(3).columnwidth = 30

        Dim Operador As String = "?"
        Dim TotalDias As Integer = DateDiff(DateInterval.Day, datFecha1.Value, datFecha2.Value)
        Dim DiaInicial As Date = datFecha1.Value.AddDays(-1)
        Dim ColumnaFinal As Integer = 0

        wb.SHEETS(1).cells(5, 2).value = "Operador"
        wb.SHEETS(1).cells(5, 3).value = "Ruta"

        For i = 4 To 4 + (TotalDias * 2)
            DiaInicial = DiaInicial.AddDays(1)
            wb.SHEETS(1).cells(5, i).value = DiaInicial.ToShortDateString
            wb.SHEETS(1).columns(i).columnwidth = 12
            wb.SHEETS(1).cells(5, i).FONT.bold = True
            i = i + 1
            wb.SHEETS(1).cells(5, i).value = "Autobus"
            wb.SHEETS(1).columns(i).columnwidth = 12
            wb.SHEETS(1).cells(5, i).FONT.bold = True
            ColumnaFinal = i
        Next

        wb.SHEETS(1).cells(5, 2).FONT.bold = True
        wb.SHEETS(1).cells(5, 3).FONT.bold = True

        rng = rng + 1
        Dim Ruta As String = "?"
        Dim FechaActual As Date
        Dim FechaRegistro As Date
        Dim CambioOperador As Boolean = False


        Do While rdBuscar.Read()
            If Operador <> rdBuscar("NombreOperador") Then
                rng = rng + 1
                wb.SHEETS(1).cells(rng, 2).value = rdBuscar("NombreOperador")
                CambioOperador = True

            End If
            If Ruta <> rdBuscar("NombreRuta") Then
                If CambioOperador = False Then
                    rng = rng + 1
                End If
            End If
            Operador = rdBuscar("NombreOperador")
            CambioOperador = False
            Ruta = rdBuscar("NombreRuta")

            wb.SHEETS(1).cells(rng, 3).value = rdBuscar("NombreRuta")
            For i = 4 To ColumnaFinal
                FechaActual = CDate(wb.SHEETS(1).cells(5, i).value)
                FechaRegistro = rdBuscar("Fecha")
                If FechaActual.ToShortDateString = FechaRegistro.ToShortDateString Then
                    wb.SHEETS(1).cells(rng, i).value = rdBuscar("Vueltas")
                    wb.SHEETS(1).cells(rng, i + 1).value = rdBuscar("NoEconomico")
                End If
                i = i + 1
            Next

        Loop


        wb.SHEETS(1).cells(1, 2).value = EmpresaActiva
        wb.SHEETS(1).cells(1, 2).FONT.SIZE = 20
        wb.SHEETS(1).cells(2, 2).value = "Reporte de Vueltas del " & Me.datFecha1.Value.ToString("d")
        wb.SHEETS(1).cells(2, 2).FONT.SIZE = 14
        wb.SHEETS(1).cells(3, 2).value = "Impreso el " & Now().Day & "-" & Now().Month & "-" & Now().Year & " por " & UsuarioActivo
        wb.SHEETS(1).cells(3, 3).FONT.SIZE = 12

        wb.SHEETS(1).cells(1, 1).value = ""
        wb.SHEETS(1).columns(1).columnwidth = 3
        wb.sheets(1).cells(1, 1).interior.color = 15773696
        wb.sheets(1).cells(2, 1).interior.color = 15773696
    End Sub
    Sub Autobus()
        Dim xl As Object
        Dim wb As Object
        Dim i As Integer
        xl = CreateObject("Excel.Application")
        xl.Visible = True
        xl.Workbooks.Add()
        wb = xl.activeworkbook
        Dim rng As Integer
        rng = 5

        wb.SHEETS(1).cells(1, 1).value = "Imprimiendo..."
        wb.SHEETS(1).columns(1).columnwidth = 15

        Dim strFecha1 As String = datFecha1.Value.ToString("yyyy-dd-MM")
        Dim strFecha2 As String = datFecha2.Value.AddDays(1).ToString("yyyy-dd-MM")

        Dim cnconn As New SqlConnection
        cnconn.ConnectionString = CitraConnection
        Dim strSql As String = ""
        cnconn.Open()
        Dim cmdBuscar As New SqlCommand
        cmdBuscar.Connection = cnconn

        Dim t As Integer = lstA.Items.Count
        Dim strBUSES As String = "'"
        For i = 0 To t - 2
            strBUSES = strBUSES & Me.lstA.Items(i).ToString.Trim & "','"
        Next
        strBUSES = strBUSES & Me.lstA.Items(t - 1).ToString.Trim & "'"

        strSql = " SELECT IdLiquidacion,NombreRuta,NoEconomico,CodigoOperador,NombreOperador,Vueltas,Fecha " & _
                 " FROM Liquidacion " & _
                 " WHERE Fecha>='" & strFecha1 & "' AND Fecha<='" & strFecha2 & "' AND NoEconomico IN (" & strBUSES & ") " & _
                 " ORDER BY NombreOperador,NombreRuta"

        cmdBuscar.CommandText = strSql
        Dim rdBuscar As SqlDataReader
        rdBuscar = cmdBuscar.ExecuteReader
        Dim strCamion As String = "?"

        wb.SHEETS(1).columns(2).columnwidth = 40
        wb.SHEETS(1).columns(3).columnwidth = 30

        Dim Operador As String = "?"
        Dim TotalDias As Integer = DateDiff(DateInterval.Day, datFecha1.Value, datFecha2.Value)
        Dim DiaInicial As Date = datFecha1.Value.AddDays(-1)
        Dim ColumnaFinal As Integer = 0

        wb.SHEETS(1).cells(5, 2).value = "Operador"
        wb.SHEETS(1).cells(5, 3).value = "Ruta"

        For i = 4 To 4 + (TotalDias * 2)
            DiaInicial = DiaInicial.AddDays(1)
            wb.SHEETS(1).cells(5, i).value = DiaInicial.ToShortDateString
            wb.SHEETS(1).columns(i).columnwidth = 12
            wb.SHEETS(1).cells(5, i).FONT.bold = True
            i = i + 1
            wb.SHEETS(1).cells(5, i).value = "Autobus"
            wb.SHEETS(1).columns(i).columnwidth = 12
            wb.SHEETS(1).cells(5, i).FONT.bold = True
            ColumnaFinal = i
        Next

        wb.SHEETS(1).cells(5, 2).FONT.bold = True
        wb.SHEETS(1).cells(5, 3).FONT.bold = True

        rng = rng + 1
        Dim Ruta As String = "?"
        Dim FechaActual As Date
        Dim FechaRegistro As Date
        Dim CambioOperador As Boolean = False


        Do While rdBuscar.Read()
            If Operador <> rdBuscar("NombreOperador") Then
                rng = rng + 1
                wb.SHEETS(1).cells(rng, 2).value = rdBuscar("NombreOperador")
                CambioOperador = True

            End If
            If Ruta <> rdBuscar("NombreRuta") Then
                If CambioOperador = False Then
                    rng = rng + 1
                End If
            End If
            Operador = rdBuscar("NombreOperador")
            CambioOperador = False
            Ruta = rdBuscar("NombreRuta")

            wb.SHEETS(1).cells(rng, 3).value = rdBuscar("NombreRuta")
            For i = 4 To ColumnaFinal
                FechaActual = CDate(wb.SHEETS(1).cells(5, i).value)
                FechaRegistro = rdBuscar("Fecha")
                If FechaActual.ToShortDateString = FechaRegistro.ToShortDateString Then
                    wb.SHEETS(1).cells(rng, i).value = rdBuscar("Vueltas")
                    wb.SHEETS(1).cells(rng, i + 1).value = rdBuscar("NoEconomico")
                End If
                i = i + 1
            Next

        Loop


        wb.SHEETS(1).cells(1, 2).value = EmpresaActiva
        wb.SHEETS(1).cells(1, 2).FONT.SIZE = 20
        wb.SHEETS(1).cells(2, 2).value = "Reporte de Vueltas del " & Me.datFecha1.Value.ToString("d")
        wb.SHEETS(1).cells(2, 2).FONT.SIZE = 14
        wb.SHEETS(1).cells(3, 2).value = "Impreso el " & Now().Day & "-" & Now().Month & "-" & Now().Year & " por " & UsuarioActivo
        wb.SHEETS(1).cells(3, 3).FONT.SIZE = 12

        wb.SHEETS(1).cells(1, 1).value = ""
        wb.SHEETS(1).columns(1).columnwidth = 3
        wb.sheets(1).cells(1, 1).interior.color = 15773696
        wb.sheets(1).cells(2, 1).interior.color = 15773696
    End Sub

    Sub Ruta()
        Dim xl As Object
        Dim wb As Object
        Dim i As Integer
        xl = CreateObject("Excel.Application")
        xl.Visible = True
        xl.Workbooks.Add()
        wb = xl.activeworkbook
        Dim rng As Integer
        rng = 5

        wb.SHEETS(1).cells(1, 1).value = "Imprimiendo..."
        wb.SHEETS(1).columns(1).columnwidth = 15

        Dim strFecha1 As String = datFecha1.Value.ToString("yyyy-dd-MM")
        Dim strFecha2 As String = datFecha2.Value.AddDays(1).ToString("yyyy-dd-MM")

        Dim cnconn As New SqlConnection
        cnconn.ConnectionString = CitraConnection
        Dim strSql As String = ""
        cnconn.Open()
        Dim cmdBuscar As New SqlCommand
        cmdBuscar.Connection = cnconn

        Dim clasifi As Integer
        Dim t As Integer = lstR.Items.Count
        Dim strRutas As String = ""

        For i = 0 To t - 2
            clasifi = BuscarIntDatoCitra("RUTAS", "IdRuta", "Nombre='" & Me.lstR.Items(i).ToString.Trim & "'")
            strRutas = strRutas & clasifi & ","
        Next
        clasifi = BuscarIntDatoCitra("RUTAS", "IdRuta", "Nombre='" & Me.lstR.Items(t - 1).ToString.Trim & "'")
        strRutas = strRutas & clasifi

        strSql = " SELECT IdLiquidacion,NombreRuta,NoEconomico,CodigoOperador,NombreOperador,Vueltas,Fecha " & _
                 " FROM Liquidacion " & _
                 " WHERE Fecha>='" & strFecha1 & "' AND Fecha<='" & strFecha2 & "' AND IdRuta IN (" & strRutas & ") " & _
                 " ORDER BY NombreOperador,NombreRuta"

        cmdBuscar.CommandText = strSql
        Dim rdBuscar As SqlDataReader
        rdBuscar = cmdBuscar.ExecuteReader
        Dim strCamion As String = "?"

        wb.SHEETS(1).columns(2).columnwidth = 40
        wb.SHEETS(1).columns(3).columnwidth = 30

        Dim Operador As String = "?"
        Dim TotalDias As Integer = DateDiff(DateInterval.Day, datFecha1.Value, datFecha2.Value)
        Dim DiaInicial As Date = datFecha1.Value.AddDays(-1)
        Dim ColumnaFinal As Integer = 0

        wb.SHEETS(1).cells(5, 2).value = "Operador"
        wb.SHEETS(1).cells(5, 3).value = "Ruta"

        For i = 4 To 4 + (TotalDias * 2)
            DiaInicial = DiaInicial.AddDays(1)
            wb.SHEETS(1).cells(5, i).value = DiaInicial.ToShortDateString
            wb.SHEETS(1).columns(i).columnwidth = 12
            wb.SHEETS(1).cells(5, i).FONT.bold = True
            i = i + 1
            wb.SHEETS(1).cells(5, i).value = "Autobus"
            wb.SHEETS(1).columns(i).columnwidth = 12
            wb.SHEETS(1).cells(5, i).FONT.bold = True
            ColumnaFinal = i
        Next

        wb.SHEETS(1).cells(5, 2).FONT.bold = True
        wb.SHEETS(1).cells(5, 3).FONT.bold = True

        rng = rng + 1
        Dim Ruta As String = "?"
        Dim FechaActual As Date
        Dim FechaRegistro As Date
        Dim CambioOperador As Boolean = False


        Do While rdBuscar.Read()
            If Operador <> rdBuscar("NombreOperador") Then
                rng = rng + 1
                wb.SHEETS(1).cells(rng, 2).value = rdBuscar("NombreOperador")
                CambioOperador = True

            End If
            If Ruta <> rdBuscar("NombreRuta") Then
                If CambioOperador = False Then
                    rng = rng + 1
                End If
            End If
            Operador = rdBuscar("NombreOperador")
            CambioOperador = False
            Ruta = rdBuscar("NombreRuta")

            wb.SHEETS(1).cells(rng, 3).value = rdBuscar("NombreRuta")
            For i = 4 To ColumnaFinal
                FechaActual = CDate(wb.SHEETS(1).cells(5, i).value)
                FechaRegistro = rdBuscar("Fecha")
                If FechaActual.ToShortDateString = FechaRegistro.ToShortDateString Then
                    wb.SHEETS(1).cells(rng, i).value = rdBuscar("Vueltas")
                    wb.SHEETS(1).cells(rng, i + 1).value = rdBuscar("NoEconomico")
                End If
                i = i + 1
            Next

        Loop


        wb.SHEETS(1).cells(1, 2).value = EmpresaActiva
        wb.SHEETS(1).cells(1, 2).FONT.SIZE = 20
        wb.SHEETS(1).cells(2, 2).value = "Reporte de Vueltas del " & Me.datFecha1.Value.ToString("d")
        wb.SHEETS(1).cells(2, 2).FONT.SIZE = 14
        wb.SHEETS(1).cells(3, 2).value = "Impreso el " & Now().Day & "-" & Now().Month & "-" & Now().Year & " por " & UsuarioActivo
        wb.SHEETS(1).cells(3, 3).FONT.SIZE = 12

        wb.SHEETS(1).cells(1, 1).value = ""
        wb.SHEETS(1).columns(1).columnwidth = 3
        wb.sheets(1).cells(1, 1).interior.color = 15773696
        wb.sheets(1).cells(2, 1).interior.color = 15773696
    End Sub

    Private Sub ToolStripButton1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ToolStripButton1.Click

        If RadioButton1.Checked = True Then
            Todos()
            Bitacora.RegistraActividad("Imprimió el reporte de liquidacion del dia " & datFecha1.Text)
        ElseIf RadioButton2.Checked = True Then
            If lstR.Items.Count = 0 Then
                MsgBox("Debes indicar al menos una ruta", MsgBoxStyle.Exclamation, "Aviso")
                Exit Sub
            End If
            Ruta()
            Bitacora.RegistraActividad("Imprimió el reporte de liquidacion del dia " & datFecha1.Text & ", Filtrado por Ruta")
        Else
            If lstA.Items.Count = 0 Then
                MsgBox("Debes indicar al menos un autobus", MsgBoxStyle.Exclamation, "Aviso")
                Exit Sub
            End If
            Autobus()
            Bitacora.RegistraActividad("Imprimió el reporte de liquidacion del dia " & datFecha1.Text & ", Filtrado por Autobus")
        End If
    End Sub

    Private Sub ToolStripButton2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ToolStripButton2.Click
        Close()

    End Sub
End Class